﻿Public Class QuanLyTaiKhoan

    Private row_index As Integer = -1
    Private manh As String = ""
    Private dt As New DataTable

    Private Sub XoaTrang()
        manh = ""
        cbTaiKhoan.Text = String.Empty
        cbNguoiDungTen.Text = String.Empty
        txtSoTien.Text = 0
    End Sub

    Private Sub GanText(ByVal index As Integer)
        cbNguoiDungTen.Text = DataGridView1.Rows(index).Cells(1).Value
        cbTaiKhoan.Text = DataGridView1.Rows(index).Cells(3).Value
        txtSoTien.Text = DataGridView1.Rows(index).Cells(4).Value
        manh = DataGridView1.Rows(index).Cells(3).Value
    End Sub

    Private Sub QuanLyTaiKhoan_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        cbNguoiDungTen.DataSource = ProcFunc.ExecuteFuncSQL("select * from [User]")
        cbNguoiDungTen.ValueMember = "MaUser"
        cbNguoiDungTen.DisplayMember = "TenUser"

        'cbTaiKhoan.DataSource = ProcFunc.ExecuteFuncSQL("select distinct MaNganHang, TenNganHang from NganHang")
        'cbTaiKhoan.ValueMember = "MaNganHang"
        'cbTaiKhoan.DisplayMember = "TenNganHang"
        'cbTaiKhoan.SelectedIndex = -1

        Dim str As String = "select  MaNganHang, TenUser, us.MaUser, TenNganHang, SoTien" &
                            " from NganHang nh, [User] us where nh.MaUser = us.MaUser" &
                            " order by TenUser"

        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        If dt.Rows.Count > 0 Then
            DataGridView1.DataSource = dt
            GanText(0)
            row_index = 0
        End If
    End Sub

    Private Sub cbNguoiDungTen_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbNguoiDungTen.SelectedIndexChanged
        cbTaiKhoan.DataSource = ProcFunc.ExecuteFuncSQL("select distinct MaNganHang, TenNganHang from NganHang where MaUser = '" & cbNguoiDungTen.SelectedValue.ToString & "'")
        cbTaiKhoan.ValueMember = "MaNganHang"
        cbTaiKhoan.DisplayMember = "TenNganHang"
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If e.RowIndex < 0 Or e.ColumnIndex < 0 Then
            Return
        End If
        row_index = e.RowIndex
        GanText(e.RowIndex)
    End Sub

    Private Sub btnSoanLai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoanLai.Click
        XoaTrang()
    End Sub

    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        'Tao ma ngan hang
        manh = ProcFunc.ExecuteFuncSSQL("select max(MaNganHang) from NganHang").ToString()
        If manh = "" Then
            manh = "NH1"
        Else
            manh = "NH" & (manh.Substring(2, manh.Length - 2) + 1)
        End If

        If cbNguoiDungTen.Text = "" Or cbTaiKhoan.Text = "" Or txtSoTien.Text = "" Then
            MessageBox.Show("Vui lòng nhập đủ thông tin")
            Return
        End If

        Dim dr() As DataRow
        dr = dt.Select("MaUser ='" & cbNguoiDungTen.SelectedValue & "' and TenNganHang = '" & cbTaiKhoan.Text.Trim() & "'")

        If dr.Length > 0 Then
            MessageBox.Show("Tài khoản này bạn đã có")
            Return
        End If

        Dim str As String = "insert into NganHang values ('" & manh & "','" & cbNguoiDungTen.SelectedValue & "',N'" & cbTaiKhoan.Text & "'," & txtSoTien.Value.ToString() & ")"
        Try
            ProcFunc.ExecuteProcSQL(str)

            'Thêm một dòng vào Datagridview không cần load lại Data
            Dim row As DataRow
            row = dt.NewRow

            row(1) = cbNguoiDungTen.Text
            row(3) = cbTaiKhoan.Text
            row(4) = txtSoTien.Value
            row(0) = manh
            row(2) = cbNguoiDungTen.SelectedValue        

            dt.Rows.Add(row)
            DataGridView1.CurrentCell = DataGridView1.Rows(dt.Rows.Count - 1).Cells(0)
            row_index = dt.Rows.Count - 1

            MessageBox.Show("Đã thêm thành công!")
        Catch ex As Exception
            MessageBox.Show("Lỗi")
        End Try
        Return
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If manh = "" Then
            Return
        End If

        Dim res As MsgBoxResult = MsgBox("Bạn có muốn xóa", MsgBoxStyle.YesNo, "Thông báo")
        Select Case res
            Case MsgBoxResult.Yes

                Dim str = "delete from NganHang where MaNganHang = '" & manh & "'"
                ProcFunc.ExecuteProcSQL(str)

                XoaTrang()
                DataGridView1.Rows.RemoveAt(row_index)

                MessageBox.Show("Dữ liệu đã được xóa thành công")

            Case MsgBoxResult.No
                Return
        End Select
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        If cbNguoiDungTen.Text <> "" And cbTaiKhoan.Text <> "" And txtSoTien.Text <> "" Then

            Dim dr() As DataRow
            dr = dt.Select("MaUser ='" & cbNguoiDungTen.SelectedValue & "' and TenNganHang = '" & cbTaiKhoan.Text.Trim() & "'")

            If dr.Length > 0 Then
                MessageBox.Show("Tài khoản này đã có")
                Return
            End If

            Dim str As String = "update NganHang set MaUser = '" & cbNguoiDungTen.SelectedValue & "', TenNganHang = N'" & cbTaiKhoan.SelectedText & "', SoTien ='" & txtSoTien.Value.ToString.Trim &
                "' where MaNganHang = '" & manh & "'"
            ProcFunc.ExecuteProcSQL(str)

            'Sua mot dong trong datagridview khong can load lai data
            Dim row As DataRow
            row = dt.Rows(row_index)
            row(1) = cbNguoiDungTen.Text
            row(3) = cbTaiKhoan.Text
            row(4) = txtSoTien.Value
            row(0) = manh
            row(2) = cbNguoiDungTen.SelectedValue

            MessageBox.Show("Dữ liệu đã được sửa thành công")

        Else
            MessageBox.Show("Xin vui lòng điền vào đầy đủ thông tin")
        End If
    End Sub
End Class